<?php
/**
 * Desc:
 * Auth: Shaoming Lu<lushao1012@163.com>
 * Date: 2018/4/10
 * Time: 16:55
 */
namespace echophp\core;

use echophp\core\exception\Error;
use echophp\core\face\DbInterface;
use echophp\core\exception\DbException;
use echophp\core\FrameCore;

class Db implements DbInterface
{
    // 当前实例
    private $instance;

    private $conn;
    private $sql;
    private $table;
    private $field;
    private $where;
    private $join;
    private $alias;
    private $order;
    private $group;
    private $limit;
    private $max;
    private $min;
    private $avg;
    private $count;
    private $sum;

    public function __construct()
    {
        $this->instance = $this;
        $this->connect();
    }

    /**
     * 在销毁时释放数据库连接
     */
    public function __destruct()
    {
        $this->close_connect();
    }

    /**
     * 连接数据库
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     */
    public function connect()
    {
        try{
            $conn = mysqli_connect(DB_HOST.':'.DB_PORT, DB_USER, DB_PASS, DB_NAME);
            if(!$conn){
                throw new DbException("Can't connect to MySQL Server. ErrorMsg:  " . mysqli_connect_error(), 500);
            }
        }catch (DbException $e) {
            Error::showErr($e);// 显示异常
            exit;
        }

        // 设置字符集
        mysqli_query($conn, "set names '".DB_CHAR_SET."' ");
        mysqli_query($conn, "set character_set_client=".DB_CHAR_SET);
        mysqli_query($conn, "set character_set_results=".DB_CHAR_SET);
        $this->conn = $conn;
    }

    /**
     * 设置数据库表，数据库操作顺序最先的操作
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @param $table
     * @return $this->instance
     */
    public function table($table)
    {
        // 清楚所有设置，因为调用此方法说明重新查询
        $this->clear_setting();
        $this->table = DB_PREFIX.$table;
        return $this->instance;
    }

    /**
     * 执行原生SQL语句，所有的数据库执行语句最后都转化为SQL交由此方法处理，查询错误会抛出异常
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @param $sql
     * @return array
     */
    public function execute($sql)
    {
        try{
            $result = mysqli_query($this->conn, $sql);
            if($result === false) {// 抛出异常
                throw new DbException(mysqli_error($this->conn) . '. The SQL: '.$sql);
            }
        }catch (DbException $e) {
            Error::showErr($e);// 显示异常
        }
            $res = [];// 返回数组
            while( $row = mysqli_fetch_assoc($result) ){
                $res[] = $row;
            }
            return $res;
    }

    /**
     * 查询数据
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     */
    public function get()
    {
        $this->generalSql();
        return $this->execute($this->sql);
    }

    /**
     * 获取SQL而不执行，可以获取最后一次的查询SQL
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @return string
     */
    public function getSql()
    {
        return $this->sql;
    }

    /**
     * 生成SQL
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     */
    public function generalSql()
    {
        if(!$this->field) {
            $this->field = '*';
        }
        $where = "";
        if($this->where) {
            $where = " where ".$this->where;
        }
        $order = "";
        if($this->order) {
            $order = " ORDER BY ".$this->order;
        }
        $group = "";
        if($this->group) {
            $group = " GROUP BY ".$this->group;
        }
        $limit = "";
        if($this->limit) {
            $limit = " limit ".$this->limit;
        }

        if($this->max) {// 查询最大值操作
            $this->sql = "SELECT " . $this->max . " from " . $this->table . $this->join . $where . $group . $order . $limit;
        } else if ($this->min) {// 查询最小值操作
            $this->sql = "SELECT " . $this->min . " from " . $this->table . $this->join . $where . $group . $order . $limit;
        } else if ($this->avg) {// 查询平均值操作
            $this->sql = "SELECT " . $this->avg . " from " . $this->table . $this->join . $where . $group . $order . $limit;
        } else if ($this->count) {// 计数操作
            $this->sql = "SELECT " . $this->count . " from " . $this->table . $this->join . $where . $group . $order . $limit;
        } else if ($this->sum) {// 查询总数操作
            $this->sql = "SELECT " . $this->sum . " from " . $this->table . $this->join . $where . $group . $order . $limit;
        } else {// 普通查询操作
            $this->sql = "SELECT " . $this->field . " from " . $this->table . $this->join . $where . $group . $order . $limit;
        }
    }

    /**
     * 查询符合条件的第一条数据
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     */
    public function first()
    {
        $this->generalSql();
        $this->execute($this->sql);
    }

    /**
     * 设置查询字段， 默认全部
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @param string $field
     * @return $this->instance
     */
    public function field($field = '*')
    {
        $field_str = '';
        if (is_array($field)) {
            $field_str = implode(',', $field);
        }else{
            $field_str = $field;
        }
        $this->field  = $field_str;
        return $this->instance;
    }


    /**
     * 查询条件，数组或字符串
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @param $where
     * @return $this->instance
     */
    public function where($where)
    {
        if(!$where){
            return null;
        }
        $where_sql = "";
        if (is_array($where)) {
            $where_1 = [];
            foreach ($where as $k => $v) {
                $where_1[] = $k . "='" . $v ."'";
            }
            $where_sql = implode(' and ', $where_1);
        } else {
            $where_sql = $where;
        }
        $this->where = $where_sql;
        return $this->instance;
    }

    /**
     * 别名
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @param $alias
     * @return $this->instance
     */
    public function alias($alias)
    {
        if(!$alias){
            return null;
        }
        $this->alias = $alias;
        return $this->instance;
    }

    /**
     * 排序
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @param $order
     * @return $this->instance
     */
    public function order($order)
    {
        if(!$order){
            return null;
        }
        $this->order = $order;
        return $this->instance;
    }

    /**
     * 分组
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @param $group
     * @return $this->instance
     */
    public function group($group)
    {
        if(!$group){
            return null;
        }
        $this->group = $group;
        return $this->instance;
    }

    /**
     * 限制查询条数
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @param $limit
     * @return $this->instance
     */
    public function limit($limit)
    {
        if(!$limit){
            return null;
        }
        $this->limit = $limit;
        return $this->instance;
    }

    /**
     * 连表，二维数组，格式[['表名', '关联条件' ,'关联方式']]
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     * @param $join
     * @return $this->instance
     */
    public function join($join)
    {
        try{
            if(!is_array($join)){
                throw new DbException("Parameter must be array but give ".gettype($join));
            }
        }catch (DbException $e) {
            Error::showErr($e);
        }

        return $this->instance;
    }

    /**
     * 查询最大值
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @param $column
     */
    public function max($column)
    {
        $this->max = 'MAX('.$column.')';
    }

    /**
     * 查询最小值
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @param $column
     */
    public function min($column)
    {
        $this->min = 'MIN('.$column.')';
    }

    /**
     * 获取平均值
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @param $column
     */
    public function avg($column)
    {
        $this->avg = 'AVG('.$column.')';
    }

    /**
     * 获取数量
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @param $column
     */
    public function count($column)
    {
        $this->count = 'COUNT('.$column.')';
    }

    /**
     * 求和
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @param $column
     */
    public function sum($column)
    {
        $this->sum = 'SUM('.$column.')';
    }

    public function insertAll($data)
    {
        try{
            // 判断参数是否为二维数组
            if(!is_array($data)){
                throw new DbException("Function insert parameter must be array but give ".FrameCore::get_type($data));
            }else if (!FrameCore::is_two_dimensional_array($data)) {
                throw new DbException("Function insert parameter must be two-dimensional array");
            }
        }catch (DbException $e) {
            Error::showErr($e);
        }

        $keys = "";
        $values = [];
        foreach ($data as $k => $v) {
            $temp_keys = [];
            $temp_values = [];
            foreach ($v as $kk => $vv) {
                $temp_keys[] = $kk;
                $temp_values[] = "'".addslashes($vv)."'";//字符转义
            }
            $keys = implode(",", $temp_keys);
            $values[] = implode(",", $temp_values);
        }

        $value = implode('),(', $values);
        $this->sql = "INSERT INTO " . $this->table . " (" . $keys . ") VALUES (" . $value . ")";
       $this->execute($this->sql);
    }

    /**
     * 关闭数据库连接
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     * @method  GET
     */
    public function close_connect()
    {
        if($this->conn){
            mysqli_close($this->conn);
        }
    }

    /**
     * 清楚所有设置，用于一次实例化执行多次查询
     * @access  public
     * @author  Lu Shaoming<lushao1012@163.com>
     */
    public function clear_setting()
    {
        $this->field = null;
        $this->order = null;
        $this->group = null;
        $this->limit = null;
        $this->where = null;
        $this->join = null;
        $this->alias = null;
    }


}